-- Recursively sorts hierarchy into alpha order.
-- Called by asi_HierarchyAlphaResort.
CREATE PROCEDURE [dbo].[asi_HierarchyAlphaResortChildren]
    @parentHierarchyKey uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO #Reorder (HierarchyKey) VALUES (@parentHierarchyKey)

    DECLARE @ChildrenTable TABLE
    (
        [HierarchyKey] uniqueidentifier,
        [DocumentName] nvarchar(100),
        [DocumentTypeCode] nvarchar(3)
    )

    INSERT INTO @ChildrenTable
        SELECT h.[HierarchyKey], d.[DocumentName], d.[DocumentTypeCode]
          FROM [dbo].[Hierarchy] h INNER JOIN [dbo].[DocumentMain] d ON h.UniformKey = d.DocumentVersionKey
         WHERE h.[ParentHierarchyKey] = @parentHierarchyKey

    DECLARE @childHierarchyKey uniqueidentifier
    DECLARE @childDocumentType nvarchar(3)
    
    WHILE EXISTS (SELECT 1 FROM @ChildrenTable)
    BEGIN
        SELECT TOP 1 @childHierarchyKey = [HierarchyKey], @childDocumentType = [DocumentTypeCode]
          FROM @ChildrenTable ORDER BY [DocumentName]
        IF (@childDocumentType != 'NAV')
            EXEC asi_HierarchyAlphaResortChildren @childHierarchyKey
        ELSE
            EXEC asi_HierarchyResortChildren @childHierarchyKey
        DELETE FROM @ChildrenTable WHERE [HierarchyKey] = @childHierarchyKey
    END

    SET NOCOUNT OFF
END

GO